﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;

using WhereIsMyCash.IDAL;
using WhereIsMyCash.Entity;
using WhereIsMyCash.Enums;
using WhereIsMyCash.Data;

namespace WhereIsMyCash.SQLServerDAL
{
    public class Account : IAccount
    {
        #region 私有成员

        /// <summary>
        /// 绑定实体数据
        /// </summary>
        /// <param name="account">实体</param>
        /// <param name="reader">IDataReader</param>
        void BindEntity(AccountInfo account, IDataReader reader)
        {
            object objData;

            objData = reader["AccountID"];
            if (objData != DBNull.Value)
            {
                account.ID = (int)objData;
            }

            account.Name = reader["AccountName"].ToString();

            objData = reader["UserID"];
            if (objData != DBNull.Value)
            {
                account.UserID = (int)objData;
            }

            objData = reader["AccountingDate"];
            if (objData != DBNull.Value)
            {
                account.AccountDate = (DateTime)objData;
            }

            account.Address = reader["Address"].ToString();
            account.Zip = reader["Zip"].ToString();

            objData = reader["ContactID"];
            if (objData != DBNull.Value)
            {
                account.ContactID = (int)objData;
            }

            account.Phone = reader["Phone"].ToString();
            account.Remark = reader["Remark"].ToString();

            objData = reader["IsDel"];
            if (objData != DBNull.Value)
            {
                account.IsDel = Convert.ToBoolean(reader["IsDel"]);
            }
        }

        /// <summary>
        /// 根据sql语句返回账户实体
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        AccountInfo GetEntityBySQL(string sql)
        {
            AccountInfos accounts = GetEntitisBySQL(sql);
            
            if (accounts.Count > 0)
            {
                return accounts[0];
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 根据sql语句返回实体列表
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        AccountInfos GetEntitisBySQL(string sql)
        {
            AccountInfos accounts = new AccountInfos();

            using (IDataReader reader = SqlHelper.ExecuteReader(CommandType.Text, sql))
            {
                try
                {
                    while (reader.Read())
                    {
                        AccountInfo account = new AccountInfo();

                        BindEntity(account, reader);
                        accounts.Add(account);
                    }
                }
                catch
                {
                    //记录错误
                    throw;
                }
                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                    }
                }
            }

            return accounts;
        }

        #endregion

        #region ICRUD<AccountInfo> 成员

        /// <summary>
        /// 是否存在
        /// </summary>
        /// <param name="entity">账户实体</param>
        /// <param name="checkType">检测类型</param>
        /// <returns></returns>
        bool ICRUD<AccountInfo>.IsExist(AccountInfo entity, WhereIsMyCash.Enums.EnumExsistingCheckType checkType)
        {
            bool isExist = false;
            string sql = string.Empty;
            object objData = null;
            SqlParameter[] parms = { SqlHelper.MakeInParam("@AccountID", SqlDbType.Int, 4, entity.ID), SqlHelper.MakeInParam("@AccountName", SqlDbType.NVarChar, 50, entity.Name) };

            switch (checkType)
            {
                case EnumExsistingCheckType.一般性检测:
                    sql = "SELECT 1 FROM [Account] WHERE [AccountID] = @AccountID";
                    break;
                case EnumExsistingCheckType.添加时检测:
                    sql = "SELECT 1 FROM [Account] WHERE [AccountName] = @AccountName";
                    break;
                case EnumExsistingCheckType.更新时检测:
                    sql = "SELECT 1 FROM [Account] WHERE [AccountName] = @AccountName AND [AccountID] <> @AccountID";
                    break;
                default:
                    break;
            }

            objData = SqlHelper.ExecuteScalar(CommandType.Text, sql, parms);

            if (null != objData)
            {
                isExist = true;
            }

            return isExist;
        }

        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="entity">账户实体</param>
        /// <returns>添加成功后的ID</returns>
        int ICRUD<AccountInfo>.Add(AccountInfo entity)
        {
            int id = 0;
            object objData = null;
            StringBuilder sql = new StringBuilder();
            SqlParameter[] parms = 
            { 
                SqlHelper.MakeInParam("@AccountName", SqlDbType.NVarChar, 100, entity.Name),
                SqlHelper.MakeInParam("@UserID", SqlDbType.Int, 4, entity.UserID),
                SqlHelper.MakeInParam("@AccountingDate", SqlDbType.DateTime, 8, entity.AccountDate),
                SqlHelper.MakeInParam("@Address", SqlDbType.NVarChar, 150, entity.Address),
                SqlHelper.MakeInParam("@Zip", SqlDbType.VarChar, 10, entity.Zip),
                SqlHelper.MakeInParam("@ContactID", SqlDbType.Int, 4, entity.ContactID),
                SqlHelper.MakeInParam("@Phone", SqlDbType.VarChar, 20, entity.Phone),
                SqlHelper.MakeInParam("@Remark", SqlDbType.NText, 0, entity.Remark)
            };

            sql.Append("INSERT INTO [Account] ")
                .Append("([AccountName],[UserID],[AccountingDate],[Address],[Zip],[ContactID],[Phone],[Remark]) ")
                .Append("VALUES (@AccountName,@UserID,@AccountingDate,@Address,@Zip,@ContactID,@Phone,@Remark) ")
                .Append("SELECT @@IDENTITY");

            objData = SqlHelper.ExecuteScalar(CommandType.Text, sql.ToString(), parms);

            if (null != objData)
            {
                id = (int)objData;
            }

            return id;
        }

        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="entity">账户实体</param>
        void ICRUD<AccountInfo>.Update(AccountInfo entity)
        {
            StringBuilder sql = new StringBuilder();
            SqlParameter[] parms = 
            { 
                SqlHelper.MakeInParam("@AccountID", SqlDbType.Int, 4, entity.ID),
                SqlHelper.MakeInParam("@AccountName", SqlDbType.NVarChar, 100, entity.Name),
                SqlHelper.MakeInParam("@Address", SqlDbType.NVarChar, 150, entity.Address),
                SqlHelper.MakeInParam("@Zip", SqlDbType.VarChar, 10, entity.Zip),
                SqlHelper.MakeInParam("@ContactID", SqlDbType.Int, 4, entity.ContactID),
                SqlHelper.MakeInParam("@Phone", SqlDbType.VarChar, 20, entity.Phone),
                SqlHelper.MakeInParam("@Remark", SqlDbType.NText, 0, entity.Remark)
            };

            sql.Append("UPDATE [Account] ")
                .Append("SET ")
                .Append("[AccountName] = @AccountName, ")
                .Append("[Address] = @Address, ")
                .Append("[Zip] = @Zip, ")
                .Append("[ContactID] = @ContactID, ")
                .Append("[Phone] = @Phone, ")
                .Append("[Remark] = @Remark")
                .Append("WHERE [AccountID] = @AccountID");

            SqlHelper.ExecuteNonQuery(CommandType.Text, sql.ToString(), parms);
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="Id"></param>
        void ICRUD<AccountInfo>.Delete(int Id)
        {
            string sql = "UPDATE [Account] SET [IsDel] = 1 WHERE [AccountID] = @AccountID";
            SqlParameter[] parms = { SqlHelper.MakeInParam("@AccountID", SqlDbType.Int, 4, Id) };

            SqlHelper.ExecuteNonQuery(CommandType.Text, sql, parms);
        }

        /// <summary>
        /// 返回账户实体
        /// </summary>
        /// <param name="Id">账户ID</param>
        /// <returns></returns>
        AccountInfo ICRUD<AccountInfo>.GetInfo(int Id)
        {
            AccountInfo account = null;
            StringBuilder sql = new StringBuilder();

            sql.Append("SELECT [AccountID],[AccountName],[UserID],[AccountingDate],[Address],[Zip],[ContactID],[Phone],[Remark],[IsDel] ")
                .Append("FROM [Account] ")
                .AppendFormat("WHERE [AccountID] = {0}",Id);

            account = GetEntityBySQL(sql.ToString());

            return account;
        }

        /// <summary>
        /// 返回账户列表
        /// </summary>
        /// <param name="condition">条件</param>
        /// <returns></returns>
        List<AccountInfo> ICRUD<AccountInfo>.GetList(string condition)
        {
            AccountInfos accounts = new AccountInfos();
            StringBuilder sql = new StringBuilder();

            sql.Append("SELECT [AccountID],[AccountName],[UserID],[AccountingDate],[Address],[Zip],[ContactID],[Phone],[Remark],[IsDel] ")
                .Append("FROM [Account] ")
                .AppendFormat("WHERE {0}", condition);

            accounts = GetEntitisBySQL(sql.ToString());

            return accounts;
        }

        /// <summary>
        /// 返回账户列表
        /// </summary>
        /// <param name="pageSize">分页大小</param>
        /// <param name="curPage">当前页码</param>
        /// <param name="condition">条件</param>
        /// <returns></returns>
        List<AccountInfo> ICRUD<AccountInfo>.GetList(int pageSize, int curPage, string condition)
        {
            AccountInfos accounts = new AccountInfos();
            StringBuilder sql = new StringBuilder();

            sql.AppendFormat("SELECT TOP {0} [AccountID],[AccountName],[UserID],[AccountingDate],[Address],[Zip],[ContactID],[Phone],[Remark],[IsDel] ", pageSize)
                .Append("FROM [Account] ")
                .Append("WHERE ")
                .Append("[AccountID] NOT IN( ")
                .AppendFormat("SELECT TOP {0} [AccountID] ", pageSize * (curPage - 1))
                .Append("FROM [Account] ")
                .AppendFormat("WHERE {0} )", condition)
                .AppendFormat("AND {0} )", condition);

            accounts = GetEntitisBySQL(sql.ToString());

            return accounts;
        }

        /// <summary>
        /// 返回记录条数
        /// </summary>
        /// <param name="condition">条件</param>
        /// <returns></returns>
        int ICRUD<AccountInfo>.GetCount(string condition)
        {
            object objDate = null;
            string sql = string.Format("SELECT COUNT(*) FROM [Account] WHERE {0}", condition);

            objDate = SqlHelper.ExecuteScalar(CommandType.Text, sql);

            return (int)(objDate ?? 0);
        }

        #endregion
    }
}
